﻿CREATE PROCEDURE [dbo].[UpdateDisksForUser]
	@iid uniqueidentifier
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	declare @lastsubmit datetime = (
		select top 1 [Submission]
		from [UserDiskInfos]
		where [InstanceId]=@iid
		order by [Submission] desc
		);

	merge [UserDiskInfosLatest] t
	using (select * from [UserDiskInfos] where [InstanceId]=@iid and [Submission]=@lastsubmit) s
	on t.[InstanceId]=s.[InstanceId]
	and t.[InstanceId]=@iid
	and t.[ListIndex]=s.[ListIndex]
	when matched then
		update set [Submission]=s.[Submission], [ListIndex]=s.[ListIndex],
			[DiskId]=s.[DiskId], [WhsDiskInfoId]=s.[WhsDiskInfoId],
			[BusType]=s.[BusType], [Capacity]=s.[Capacity],
			[FreeSpace]=s.[FreeSpace], [IsExternal]=s.[IsExternal],
			[DiskModel]=s.[DiskModel], [DiskRole]=s.[DiskRole],
			[UsedSpace]=s.[UsedSpace]
	when not matched by source and t.[InstanceId]=@iid then
		delete
	when not matched by target then
		insert ([InstanceId],[Submission],[ListIndex],[DiskId],[WhsDiskInfoId],[BusType],[Capacity],[FreeSpace],[IsExternal],[Location],[DiskModel],[DiskRole],[UsedSpace])
		values (s.[InstanceId],s.[Submission],s.[ListIndex],s.[DiskId],s.[WhsDiskInfoId],s.[BusType],s.[Capacity],s.[FreeSpace],s.[IsExternal],s.[Location],s.[DiskModel],s.[DiskRole],s.[UsedSpace])
	;
END
GO
